-- Select your application's database in phpMyAdmin, then run this once.
-- Existing product rows and category rows are preserved.
CREATE TABLE IF NOT EXISTS categories (
    category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_category_name (category_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @category_column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'category_id'
);
SET @category_sql = IF(@category_column_exists = 0,
    'ALTER TABLE products ADD COLUMN category_id INT NULL DEFAULT NULL, ADD INDEX idx_product_category (category_id)',
    'SELECT ''products.category_id already exists'' AS message');
PREPARE category_migration FROM @category_sql;
EXECUTE category_migration;
DEALLOCATE PREPARE category_migration;
